import requests, pymysql, pandas, configparser, os


class Conf:
    conf = configparser.ConfigParser()  # 类变量

    def read_entry(self):  # 读入口配置文件
        Conf.conf.read('entry.ini', encoding='utf-8')
        self.__which_server = Conf.conf.get('entry', 'which_server')
        self.__which_db = Conf.conf.get('entry', 'which_db')
        # print(self.__which_server, self.__which_db)

    def read_server_conf(self):  # 读接口服务器信息  将接口服务器信息组装成http://IP:port的形式，向类外公开，后面用例中可以使用
        Conf.conf.read('server.conf', encoding='utf-8')
        ip = Conf.conf.get(self.__which_server, 'ip')
        port = Conf.conf.get(self.__which_server, 'port')
        self.host = f'http://{ip}:{port}'
        # print(self.host)

    def read_db_conf(self):  # 读数据库
        Conf.conf.read('db.conf', encoding='utf-8')
        host = Conf.conf.get(self.__which_db, 'host')
        db = Conf.conf.get(self.__which_db, 'db')
        user = Conf.conf.get(self.__which_db, 'user')
        passwd = Conf.conf.get(self.__which_db, 'passwd')
        self.dbinfo = {'host': host, 'db': db, 'user': user, 'passwd': passwd}
        # print(self.dbinfo)

    def __init__(self):  # 构造方法
        self.read_entry()
        self.read_server_conf()
        self.read_db_conf()


def read_sqls(*sqlfiles):  # 读指定的sql文件sqlfile将所有有效sql语句存入列表并返回；如果不给参数则读取当前文件夹中所有sql文件中的sql语句
    # file=open(sqlfiles,'r',encoding='utf-8')
    # sqls=[]  #存所有有效sql语句的列表
    # for sql in file:
    #     if sql.strip() and not sql.startswith('--'):
    #         sqls.append(sql.strip())
    # return sqls
    # print(read_sqls('login.sql'))

    # def read_sqls(*sqlfiles):
    if not sqlfiles:  # 没给参数
        sqlfiles = [i for i in os.listdir() if i.endswith('.sql')]
    # print(sqlfiles)
    sqls = []
    # 否则，sqlfiles中本来就存着很多文件名
    for sfile in sqlfiles:
        file = open(sfile, 'r', encoding='utf-8')
        for sql in file:
            if sql.strip() and not sql.startswith('--'):
                sqls.append(sql.strip())
    return sqls


# print(read_sqls('login.sql'))
# print(read_sqls())

class DB:  # 构造方法，用于连接数据库，将数据库连接对象和游标存入私有成员变量
    def __init__(self):  # 构造方法
        dbinfo = Conf().dbinfo
        self.__conn = pymysql.connect(**dbinfo)
        self.__cursor = self.__conn.cursor()

    def init_db(self, *sqlfiles):  # 初始化数据库
        sqls = read_sqls(*sqlfiles)
        # print(sqls)
        for sql in sqls:
            self.__cursor.execute(sql)
        self.__conn.commit()

    def checkdb(self, case_info, args, check_sql, expect_rows):  # 落库检查
        self.__cursor.execute(check_sql)
        actual_rows = self.__cursor.fetchone()[0]
        if actual_rows == expect_rows:
            print(f'{case_info}==落库检查通过')
        else:
            print(f'{case_info}==落库检查失败==要检查的数据：{args}==预期行数：{expect_rows}==实际行数：{actual_rows}')
            self.__conn.close()


def read_cases(xlsfile, columns=None):
    data = pandas.read_excel(xlsfile, usecols=columns)
    data['参数'] = data['参数'].apply(eval)
    data['预期结果'] = data['预期结果'].apply(eval)
    case_info = data['用例编号'] + ':' + data['用例说明']
    url = Conf().host + data['接口路径']
    data.insert(0, '用例信息', case_info)
    data.insert(1, '接口地址', url)
    data.drop(['用例编号', '用例说明', '接口路径'], axis=1, inplace=True)
    cases = data.values.tolist()
    return cases
def send_request(url,method,request_type,args):  #url是接口地址，method是请求方法，request_type是请求类型，args是请求参数，
    send=f"requests.{method}('{url}',{request_type}={args})" #返回值是响应类型（在响应头中）、响应正文
    res=eval(send)
    if 'json'in res.headers['content-type']:
        res_type='json'
        actual=res.json()
    elif 'text' in res.headersp['content-type']:
        res_type='text'
        actual=res.text
    else:   #暂不处理其他类型
        pass
    return res_type,actual
def check(case_info,res_type,actual,expect):
    passed = False  # 测试是否通过,假设测试失败
    if res_type == 'text'and expect in actual:
        passed=True
    elif res_type=='json' and expect==actual:
        passed=True
    else:  #其他情况暂时跳过
        pass
    if passed:
        print(f'{case_info}==响应结果比对通过')
    else:
        print(f'{case_info}==响应结果比对失败==预期：{expect}==实际：{actual}')

#登录接口测试函数
def login():
    DB().init_db('login.sql')
    cases=read_cases('login.xlsx',['用例编号', '用例说明', '接口路径','请求方法','请求类型', '参数', '预期结果'])
    for case in cases:
        case_info,url,method,request_type,args,expect=case
        res_type,actual=send_request(url,method,request_type,args)
        check(case_info,res_type,actual,expect)
def signup():
    DB().init_db('signup.sql')
    cases=read_cases('signup.xlsx',['用例编号', '用例说明', '接口路径','请求方法','请求类型', '参数', '预期结果','验库sql','预期行数'])
    for case in cases:
        case_info,url,method,request_type,args,expect,check_sql,expect_rows=case
        res_type,actual=send_request(url,method,request_type,args)
        check(case_info,res_type,actual,expect)  #检查响应结果是否正确
        DB().checkdb(case_info,args,check_sql,expect_rows)

if __name__ == '__main__':  # 调试代码
    a = DB()
    a.init_db()
    # Conf().read_entry()  # Conf()创建一个匿名对象
    # 写法1：没写构造之前
    # a=Conf()
    # a.read_entry()
    # a.read_server_conf()
    # a.read_db_conf()
    # 写法2：使用构造
    # a = Conf()
    # print(a.host, a.dbinfo)
    # a = DB()
    # a.init_db()  # sqlfiles=(),read_sqls(*()),read_sqls()
    # a.checkdb('检查总行数', {'s': 23}, 'select count(*) from users', 6)
    a.checkdb('检查总行数', {'s': 23}, 'select count(*) from users', 7)


    # print(read_cases('login.xlsx', ['用例编号', '用例说明', '接口路径', '参数', '预期结果']))
    print(send_request('http://192.168.196.128/apitest/login/','post','data',{'username':'test01','password':'123456'}))
    print(send_request('http://192.168.196.128/apitest/signup/','post','json',{'username':'test01','password':'123456','confirm':'123456','name':'测试一'}))
    check('用例信息','json',{'a':1,'b':2},{'a':1,'b':2})
    check('用例信息', 'json', {'a': 1, 'b': 2}, {'a': 1, 'b': 3})
    # login()
    signup()